package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import Entity.Products;

public class ProductsDAO {

    // 插入数据
    public void addProduct(Products product) {
        String sql = "INSERT INTO Products (name, identifier, category, groupid, locationx, locationy, stock) VALUES (?, ?, ?, ?, ?, ?, ?)";
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, product.getName());
            stmt.setInt(2, product.getId());
            stmt.setString(3, product.getCategory());
            stmt.setInt(4, product.getGroupId());
            stmt.setInt(5, product.getX());
            stmt.setInt(6, product.getY());
            stmt.setInt(7, product.getStock());
            stmt.executeUpdate();
            System.out.println("Product inserted successfully!");
        } catch (SQLException e) {
            System.err.println("Error inserting product: " + e.getMessage());
            e.printStackTrace();
        }
    }

    // 查询单个数据
    public Products getProductById(Integer identifier) {
        String sql = "SELECT * FROM Products WHERE identifier = ?";
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, identifier);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    Products product = new Products();
                    product.setName(rs.getString("name"));
                    product.setId(rs.getInt("identifier"));
                    product.setCategory(rs.getString("category"));
                    product.setGroupId(rs.getInt("groupid"));
                    product.setX(rs.getInt("locationx"));
                    product.setY(rs.getInt("locationy"));
                    product.setStock(rs.getInt("stock"));
                    return product;
                }
            }
        } catch (SQLException e) {
            System.err.println("Error retrieving product: " + e.getMessage());
            e.printStackTrace();
        }
        return null;
    }

    // 查询所有数据
    public List<Products> getAllProducts() {
        String sql = "SELECT * FROM Products";
        List<Products> productsList = new ArrayList<>();
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            while (rs.next()) {
                Products product = new Products();
                product.setName(rs.getString("name"));
                product.setId(rs.getInt("identifier"));
                product.setCategory(rs.getString("category"));
                product.setGroupId(rs.getInt("groupid"));
                product.setX(rs.getInt("locationx"));
                product.setY(rs.getInt("locationy"));
                product.setStock(rs.getInt("stock"));
                productsList.add(product);
            }
        } catch (SQLException e) {
            System.err.println("Error retrieving products: " + e.getMessage());
            e.printStackTrace();
        }
        return productsList;
    }

    // 更新数据
    public void updateProduct(Products product) {
        String sql = "UPDATE Products SET name = ?, category = ?, groupid = ?, locationx = ?, locationy = ?, stock = ? WHERE identifier = ?";
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, product.getName());
            stmt.setString(2, product.getCategory());
            stmt.setInt(3, product.getGroupId());
            stmt.setInt(4, product.getX());
            stmt.setInt(5, product.getY());
            stmt.setInt(6, product.getStock());
            stmt.setInt(7, product.getId());
            stmt.executeUpdate();
            System.out.println("Product updated successfully!");
        } catch (SQLException e) {
            System.err.println("Error updating product: " + e.getMessage());
            e.printStackTrace();
        }
    }

    // 删除数据
    public void deleteProduct(Integer identifier) {
        String sql = "DELETE FROM Products WHERE identifier = ?";
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, identifier);
            stmt.executeUpdate();
            System.out.println("Product deleted successfully!");
        } catch (SQLException e) {
            System.err.println("Error deleting product: " + e.getMessage());
            e.printStackTrace();
        }
    }
    public Products getProductByGroupId(int groupId) {
        // 查询数据库，返回指定 groupId 的产品
        String sql = "SELECT * FROM Products WHERE groupid = ?";
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, groupId);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                // 假设 Products 类有对应的构造方法或 setter 方法来初始化对象
                return new Products(
                        rs.getString("name"),
                        rs.getInt("identifier"),
                        rs.getString("category"),
                        rs.getInt("groupid"),
                        rs.getInt("stock"),
                        rs.getInt("locationx"),
                        rs.getInt("locationy"),
                        null // 你可以根据需要设置 counter
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;  // 如果没有找到相应的产品
    }
}
